import tushare as ts
import pymysql
import pandas


def create_table(table_name):
    db = pymysql.connect('localhost','root','minicooper','stock_info',charset='utf8')
    cursor = db.cursor()
    create_table_sql = """
                        CREATE TABLE IF NOT EXISTS {0} (
                        CODE CHAR(6),
                        NAME TEXT,
                        INDUSTRY TEXT,
                        AREA TEXT,
                        OUTSTANDING FLOAT,
                        TOTALS FLOAT,
                        TIMETOMARKET CHAR(8)
                        )
                        """.format(table_name)
    cursor.execute(create_table_sql)
    db.close()


def insert_basics_table(table_name,data):
    db = pymysql.connect('localhost','root','minicooper','stock_info',charset='utf8')
    cursor = db.cursor()

    code = data.name
    name = data['name']
    industry = data['industry']
    area = data['area']
    outstanding = data['outstanding']
    totals = data['totals']
    timetomarket = data['timeToMarket']
    insert_data = (code,name,industry,area,outstanding,totals,timetomarket)

    query_table_sql = "SELECT * FROM {0} WHERE CODE = {1}".format(table_name,code)

    insert_table_sql = "INSERT INTO {0} (CODE,NAME,INDUSTRY,AREA,OUTSTANDING,TOTALS,TIMETOMARKET) VALUES {1}".format(table_name,insert_data)

    update_table_sql = "UPDATE {0} SET NAME = {1} INDUSTRY = {2} AREA = {3} OUTSTANDING = {4} TOTALS = {5} TIMETOMARKET = {6} WHERE CODE = {7}".format(table_name,name,industry,area,outstanding,totals,timetomarket,code)

    cursor.execute(query_table_sql)
    result = cursor.fetchone()

    if result == None:
        cursor.execute(insert_table_sql)
        db.commit()
    else:
        cursor.execute(update_table_sql)
        db.commit()

    db.close()

if __name__ == '__main__':
    stock_name = 'stock_basics'
    create_table(stock_name)
    df = ts.get_stock_basics()
    for i in range(len(df)):
        insert_basics_table(stock_name,df.iloc[i])